from urllib.request import urlopen
import re
import json
# 导入pymysql模块
import pymysql

homepage = "https://www.dytt8.net"

def get_dytt_url(homepage):
    index_content = urlopen(homepage).read().decode("gbk")  # 获取电影天堂主页源代码
    #index_obj = re.compile(r"最新电影下载</a>]<a href='(?P<film_url>.*?)'>", re.S)  # 2019新片精品链接
    index_obj = re.compile(r"<a href='(?P<film_url>.*?)'>.*?</a><br/>", re.S)  # 2019新片精品链接
    return index_obj.finditer(index_content)  # 创建一个迭代器

def get_dytt_list_url(listpage):
    print(listpage)
    index_content = urlopen(listpage).read().decode("gbk")  # 获取电影天堂主页源代码
    index_obj = re.compile(r"<a href=\"(?P<film_url>.*?)\" class=\"ulink\">.*?</a>", re.S)
    #index_obj = re.compile(r"最新电影下载</a>]<a href='(?P<film_url>.*?)'>", re.S)  # 2019新片精品链接
    return index_obj.finditer(index_content)  # 创建一个迭代器

def get_dytt_info(film_url_ite):
    for k, fil_url in enumerate(film_url_ite, 1):  # 遍历电影每个url
        print(k, homepage + fil_url.group("film_url"))
        film_content = urlopen(homepage + fil_url.group("film_url")).read().decode("gbk",errors="ignore")  # 获取2019新片精品源代码
        film_obj = re.compile(r'<img border="0" src="(?P<pic_url>.*?)" alt="" />.*?'
                              r'◎译　　名(?P<trans_name>.*?)<br />.*?◎片　　名(?P<film_name>.*?)<br />.*?'
                              r'◎豆瓣评分　(?P<movie_score>.*?)/10 from .*?'
                              r'◎主　　演(?P<film_actor>.*?)<br /><br />.*?<td style="WORD-WRAP: break-word" '
                              r'bgcolor="#fdfddf"><a href="(?P<down_url>.*?)">', re.S)
        yield film_obj.finditer(film_content)  # 生成器函数

def record_dytt_info(film_ite, json_file, i):
    for film in film_ite:  # 遍历每部电影
        film_dict = {}
        film_dic = {}
        film_dic['trans_name'] = film.group("trans_name").strip().replace('&middot;', '·')  # 电影译名放入字典
        film_dic['film_name'] = film.group("film_name").strip().replace('&middot;', '·')  # 电影名放入字典
        film_dic['pic_url'] = film.group("pic_url").strip()
        film_dic['movie_score'] = film.group("movie_score").strip()
        farts_lst = film.group("film_actor").strip().split('<br />')  # 主演列表

        farts = []
        for art in farts_lst:  # 遍历主演
            farts.append(art.strip().replace('&middot;', '·'))  # 字符串替换
        film_dic['film_actor'] = farts  # 主演放入字典
        film_dic['down_url'] = film.group("down_url").strip()  # 下载url放入字典

        # 连接database
        conn = pymysql.connect(
            host="129.28.126.219",
            user="root",
            password="123456",
            database="movies",
            charset="utf8")

        # 得到一个可以执行SQL语句的光标对象
        cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
        # 得到一个可以执行SQL语句并且将结果作为字典返回的游标
        # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        # 定义要执行的SQL语句
        # ftp://ygdy8:ygdy8@yg68.dydytt.net:7005/阳光电影www.ygdy8.com.欢乐颂.BD.720p.中英双字幕.mkv
        sql = """
                INSERT INTO movies_detail (movie_name,movie_trans_name,movie_simple_name,movie_img_url,movie_url,movie_score) 
                VALUES 
                ("%s","%s","%s","%s","%s","%s") """ % (film_dic['film_name'],film_dic['trans_name'],re.findall(u"com.(.+?).m",film_dic['down_url'])[0],film_dic['pic_url'],film_dic['down_url'],film_dic['movie_score'])



        # 执行SQL语句
        cursor.execute(sql)

        # 关闭光标对象
        cursor.close()

        # 关闭数据库连接
        conn.close()

        film_dict[i] = film_dic  # 将字典放入电影数数字典
        mode_type = "w" if i == 1 else "a"  # 当i=1时,清空json文件,否则追加写入
        with open(json_file, mode=mode_type, encoding="utf-8") as f:
            f.write(json.dumps(film_dict, ensure_ascii=False)+",\n")

def filter_data():
    # 连接database
    conn = pymysql.connect(
        host="129.28.126.219",
        user="root",
        password="123456",
        database="movies",
        charset="utf8")

    # 得到一个可以执行SQL语句的光标对象
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标
    # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 定义要执行的SQL语句
    # ftp://ygdy8:ygdy8@yg68.dydytt.net:7005/阳光电影www.ygdy8.com.欢乐颂.BD.720p.中英双字幕.mkv
    sql = """DELETE FROM movies_detail WHERE movie_name IN (SELECT movie_name FROM(SELECT movie_name FROM movies_detail GROUP BY movie_name HAVING count(movie_name)>1) a)AND id NOT IN (SELECT min_id FROM (SELECT MIN(id) AS min_id FROM movies_detail GROUP BY movie_name HAVING count(movie_name)>1) b)"""

    # 执行SQL语句
    cursor.execute(sql)

    # 关闭光标对象
    cursor.close()

    # 关闭数据库连接
    conn.close()

if __name__ == "__main__":
    homepage = "https://www.dytt8.net"  # 电影天堂主页
    film_url_ite = get_dytt_url(homepage)  # 电影url迭代器
    for i, film_info_ite in enumerate(get_dytt_info(film_url_ite), 1):  # 遍历迭代器函数
        record_dytt_info(film_info_ite, "film_info.json", i)    #  获取电影的主演/名字/下载链接
    # list = "https://www.ygdy8.net/html/gndy/dyzz/list_23_{}.html"
    # for i in range(3):
    #     film_url_ite = get_dytt_list_url(list.format(i))
    #     for i, film_info_ite in enumerate(get_dytt_info(film_url_ite), 1):  # 遍历迭代器函数
    #         print(film_info_ite)
    #         record_dytt_info(film_info_ite, "film_info.json", i)
    filter_data()
